package com.example.demo.cbhs;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;

@Slf4j
public class EasyExcelCellWriteHandler implements CellWriteHandler {

    private final int start = 6;
    private int end;

    private boolean merge;

    private List<String> rtColList = new ArrayList<>();

    public EasyExcelCellWriteHandler(int dataSize) {
        this.end = start + dataSize;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //sheet页码
        //Integer sheetNo = writeSheetHolder.getSheetNo();
        //第一页
        Row row = cell.getRow();
        int rowNum = row.getRowNum() + 1;
        if (rowNum < end && rowNum >= start) {
            if (rowNum == start && !merge) {
                merge = true;
                Sheet cachedSheet = writeSheetHolder.getCachedSheet();
                cachedSheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),
                        end + 1, 0, 0));
            }
            //if (cell.getColumnIndex() == 10) {
            //    cell.setCellType(CellType.NUMERIC);
            //}
            if (cell.getColumnIndex() == 13) {
                String s = addTemp(rowNum);
                cell.setCellFormula(s);
            }
            if (cell.getColumnIndex() == 4) {
                if (!cell.getStringCellValue().contains("不包税")) {
                    rtColList.add("I" + rowNum);
                }

            }
        }
        if (rowNum == end + 11) {
            if (cell.getColumnIndex() == 17) {
                String rt = String.join("+", rtColList);
                cell.setCellFormula("(" + rt + ")*0.5/B3");
            }
        }

    }

    static String sumTemp = "I{}*IF(J{}=\"\", 0, J{})+M{}+IF(K{}=\"\", 0, K{})";

    private static String addTemp(int row) {
        return StrUtil.format(sumTemp, row, row, row, row, row, row);
        //return "I" + row + "*J" + row + "+M" + row + "+K" + row;
    }
}